/*
   Copyright 2009 the original author or authors

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
*/

package com.googlecode.kohmori.reportgen;

import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.xml.sax.SAXException;
import org.xml.sax.SAXParseException;
import org.xml.sax.helpers.DefaultHandler;

/**
    DAO for lookup list information
*/
public class JDBCConnObj {

    // Database connection for the object, will get copied
    // to other DAO objects in this package.
    private Connection dbConn;
    
    // Database driver
    protected Driver dbDriver;
    
    // Check if dbConn is active 
    private boolean isConnected = false;

    // user-readable name of database connected to
    private static ArrayList<String> databaseNames = new ArrayList<String>(20);

    // JDBC URL of database, corresponding to database names above
    private static ArrayList<String> databaseURLs = new ArrayList<String>(20);

    // user-readable name of database being connected to
    private String activeDatabaseName = null;
    
    public JDBCConnObj() throws Exception {
        if (databaseNames.isEmpty()) {        
            // See http://forums.devshed.com/archive/t-170109 for multiple ways of loading resources.
            URL dbFileURL = getClass().getClassLoader().getResource("databases.xml");
            processDatabaseXMLFile(dbFileURL);
        }
    }
    
    public JDBCConnObj(URL dbFileURL) throws Exception {
        if (databaseNames.isEmpty()) {        
            processDatabaseXMLFile(dbFileURL);
        }
    }

    public boolean connectToDatabase(String userId, String password, String databaseName) 
    throws Exception {
        isConnected = false;
    
        int databaseIdx = getDatabaseIndex(databaseName);
        if (databaseIdx < 0) {
            throw new IllegalStateException("Unknown database name.");
        }
           
        String databaseURL = null;
        
        try {
            databaseURL = databaseURLs.get(databaseIdx);
        } catch (IndexOutOfBoundsException ex) {
            throw new IllegalStateException("(Internal error) Database string " +
                    "could not be found.");
        }

        DriverManager.registerDriver(dbDriver);

        try {
            dbConn = DriverManager.getConnection(databaseURL, userId, 
                    new String(password));
        } catch (SQLException e) {
            int errCode = e.getErrorCode();
            System.out.println("error code = " + errCode + "; message = " + 
                    e.getMessage());
            
            if (errCode == 1017) {  // bad login/pwd
                throw new IllegalStateException("Invalid username and/or password.");
            } else if (errCode == 28000) {
                throw new IllegalStateException("User account is locked.");
            } else {
                e.printStackTrace();
                throw new IllegalStateException(
                        "Database connection cannot be made: " +
                        "Code: " + errCode + "; Message: " + e.getMessage());
            }           
        }
        
        activeDatabaseName = databaseName;
        
        isConnected = true;
        return isConnected;
    }

    public boolean isConnected() {
        return isConnected;
    }
          
    /** 
     *  Provides the user-readable text string for 
     *  the database chosen, for appending to the
     *  application title bar, etc.
     * 
     *  @return the user-readable name for the database as
     *  it appeared in the Database combo box.
     */
    public String getActiveDatabaseName() {
        return activeDatabaseName;
    }

    public void closeConnection() {
        if(dbConn != null) {
            try {dbConn.close();}
            catch(Exception e) {}
        }
        isConnected = false;
    }
    
    public ArrayList<String> getDatabaseNames() {
        return databaseNames;
    }
    
    public Connection getDBConn() {
        return dbConn;
    }

    public void setDbConn(Connection dbConn) {
        this.dbConn = dbConn;
    }

    private void processDatabaseXMLFile(URL dbFileURL) throws Exception {
        /* Header and DTD of databases.xml file:
            <?xml version="1.0" encoding="utf-8"?>

            <!DOCTYPE databases [ 
                <!ELEMENT databases (database+)> 
                <!ELEMENT database (#PCDATA)> 
                <!ATTLIST database url CDATA #REQUIRED>
            ]> 
            
            where url is in this format: <SERVER-URL>:<PORT>/<INSTANCE>
        */

        if (dbFileURL == null) {
            throw new Exception("(Internal error) Database configuration file " +
                "is missing.");
        }
        
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        factory.setValidating(true);

        try {
            DocumentBuilder builder = factory.newDocumentBuilder();
            builder.setErrorHandler(new DefaultHandler() {
                // throw exceptions for validation errors
                @Override
                public void error(SAXParseException e) 
                    throws SAXParseException { throw e; }
            });

            InputStream is = dbFileURL.openStream();
            Document rootNode = builder.parse(is);
            
//          Alternative way of doing this:
//          URI dbFileURI = new URI(dbFileURL.toString());      
//          Document rootNode = builder.parse(dbFileURI.toString());           
            
            Node databasesNode = null;
            // skip processing instructions, comments, etc., and get to the
            // top-level <databases> element in the XML file.
            for (int i=0; i < rootNode.getChildNodes().getLength(); i++) {
                databasesNode = rootNode.getChildNodes().item(i);
                if (databasesNode.getNodeType() == Node.ELEMENT_NODE) {
                    break;
                }
            }

            // scrolls through the <database> nodes under <databases>
            for (int i=0; i < databasesNode.getChildNodes().getLength(); i++) {
                Node elementNode = databasesNode.getChildNodes().item(i);
                if (elementNode.getNodeType() == Node.ELEMENT_NODE) { 
                    // get user-readable database name
                    Node textNode = elementNode.getFirstChild();
                    String databaseName = textNode.getNodeValue();

                    // get database URL from the url attribute
                    NamedNodeMap nnm = elementNode.getAttributes();
                    Node attrNode = nnm.item(0);
                    String urlString = attrNode.getNodeValue();

                    // add database to HashMap and ComboBox
                    databaseNames.add(databaseName);
                    databaseURLs.add(urlString);
                }
            }
        } catch (SAXException sxe) {  // Error generated during parsing
            throw new Exception("Error parsing databases.xml file -- " 
                    + sxe.getMessage());
        } catch (ParserConfigurationException pce) {
            throw new Exception("Error creating parser for databases.xml file -- " 
                    + pce.getMessage());
        } catch (IOException ioe) { // includes FileNotFoundException
            throw new Exception("Cannot find databases.xml file."); 
        } 
    }   
    
    protected int getDatabaseIndex(String databaseName) {
        int chosenDatabase = -1;
        
        if (databaseName.equalsIgnoreCase("toursDB")) {
            chosenDatabase = 0;
        } else if (databaseName.equalsIgnoreCase("toursDB2")) {
            chosenDatabase = 1;
        }
        
        return chosenDatabase;
    }

    public void setDbDriver(Driver dbDriver) {
        this.dbDriver = dbDriver;
    }

}
